﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using System.Collections.ObjectModel;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public class BookingDataAccess : IDisposable
    {
        public const string ConnString = @"Data Source=.\SQLEXPRESS;Initial Catalog=DataBaseHotel;Integrated Security=True";
        private SqlConnection cnn;
        //Buscar COindicencias en intervalos con tiempo
        public Collection<Room> SearchBooking(Booking book)
        {
            if (book == null) { throw new ArgumentException("Invalid Input Parameters"); }
            Collection<Room> roomsAvialables = new Collection<Room>();
            RoomDataAccess roomdal = new RoomDataAccess();
            roomdal.Dispose();
            Collection<Room> roomsByType = new Collection<Room>();
            roomsByType = roomdal.GetAllRoomByIdType(book.Types);
            if (roomsByType.Count == 0)
            {
                return roomsAvialables;
            }
            else
            {
                //preguntar en la base de datos cuales estan disponibles de estos cuartos
                for (int i = 0; i < roomsByType.Count; i++)
                {
                    int idRoom = roomsByType.ElementAt(i).Id;
                    if (IsAvailable(idRoom, book.CheckIn, book.Checkout)) { roomsAvialables.Add(roomsByType.ElementAt(i)); }
                }
                return roomsAvialables;
            }
        }

        public bool IsAvailable(int idRoom, DateTime checkIn, DateTime checkout)
        {
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "SELECT * From [DataBaseHotel].[dbo].[room] WHERE idRoom=@id AND @id";
            sqlSentence += " NOT IN(SELECT idRoom FROM [DataBaseHotel].[dbo].[Booking] ";

            sqlSentence += " WHERE @CheckIn  BETWEEN [Booking].[CheckIn] AND [Booking].[CheckOut] ";

            sqlSentence += " OR @CheckOut  BETWEEN [Booking].[CheckIn] AND  [Booking].[CheckOut] ) ";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@CheckIn", checkIn);
            cmd.Parameters.AddWithValue("@CheckOut", checkout);
            cmd.Parameters.AddWithValue("@id", idRoom);
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    return true;
                }
                else { return false; }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Booking A Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

        public void GetAllRoomsDataGrid(DataGridView rooms, Collection<Room> roomsList)
        {
            if (rooms == null)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            if (roomsList == null) { throw new ArgumentException("Invalid Input Parameters"); }
            for (int index = 0; index < roomsList.Count; index++)
            {
                rooms.Rows.Add();
                rooms["idRoom", index].Value = roomsList.ElementAt(index).Id.ToString(CultureInfo.CurrentCulture);
                rooms["roomName", index].Value = roomsList.ElementAt(index).Name;
            }
        }
        public bool GetAllRoomsDataGridType(DataGridView rooms, int idTypeOfRoom)
        {
            if (rooms == null)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            if (idTypeOfRoom == 0) { throw new ArgumentException("Invalid Input Parameters"); }
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "SELECT  dbo.Booking.idRoom, dbo.room.Name,dbo.room.idType,dbo.Booking.CheckIn, dbo.Booking.CheckOut From [DataBaseHotel].[dbo].[Booking] INNER JOIN [DataBaseHotel].[dbo].[room] ON dbo.room.idRoom = dbo.Booking.idRoom WHERE dbo.room.idType=@id";

            SqlCommand cmd = cnn.CreateCommand();

            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@id", idTypeOfRoom);
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                int index = 0;
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        rooms.Rows.Add();
                        rooms["Room", index].Value = reader["Name"].ToString();
                        rooms["CheckInDate", index].Value = reader["CheckIn"].ToString().Substring(0, 10);
                        rooms["CheckOutDate", index].Value = reader["CheckOut"].ToString().Substring(0, 10);
                        ++index;
                    }
                    return true;
                }
                else { return false; }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Searching Booked A Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

        public bool GetAllRoomsDataGridByDate(DataGridView rooms, DateTime checkDate, DateTime checkoutDate)
        {
            if (rooms == null)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            if (checkDate == null) { throw new ArgumentException("Invalid Input Parameters"); }
            if (checkoutDate == null) { throw new ArgumentException("Invalid Input Parameters"); }
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "SELECT dbo.room.Name,dbo.room.idRoom,CheckIn,CheckOut FROM  [DataBaseHotel].[dbo].[Booking] INNER JOIN [DataBaseHotel].[dbo].[room] ON dbo.room.idRoom = dbo.Booking.idRoom ";
            sqlSentence += " WHERE [Booking].[CheckIn] BETWEEN @CheckIn AND  @CheckOut ";

            sqlSentence += " AND  [Booking].[CheckOut] BETWEEN @CheckIn AND  @CheckOut ";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.Parameters.AddWithValue("@CheckIn", checkDate);
            cmd.Parameters.AddWithValue("@CheckOut", checkoutDate);
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.AddWithValue("@id", idTypeOfRoom);
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                int index = 0;
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        rooms.Rows.Add();
                        rooms["Room", index].Value = reader["Name"].ToString();
                        rooms["CheckInDate", index].Value = reader["CheckIn"].ToString().Substring(0, 10);
                        rooms["CheckOutDate", index].Value = reader["CheckOut"].ToString().Substring(0, 10);
                        ++index;
                    }
                    return true;
                }
                else { return false; }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Searching Booked A Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public bool BookARoom(Booking bookingRoom)
        {
            if (bookingRoom == null)
            {
                throw new ArgumentException("Error booking a room");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = @"INSERT INTO [DataBaseHotel].[dbo].[Booking]([CheckIn],[CheckOut],[ClientCI],[idRoom])VALUES (@CheckIn,@CheckOut,@ClientCi,@IdRoom)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@CheckIn", bookingRoom.CheckIn);
            cmd.Parameters.AddWithValue("@CheckOut", bookingRoom.Checkout);
            cmd.Parameters.AddWithValue("@ClientCi", bookingRoom.ClientCI);
            cmd.Parameters.AddWithValue("@IdRoom", bookingRoom.RoomBook.Id);

            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("The record wasn't registered");
                }
                else { return true; }

            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Booking A Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public bool DeleteBooking(Booking book)
        {
            if (book == null)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "DELETE FROM [DataBaseHotel].[dbo].[Booking] WHERE CheckIn = @checkIn AND CheckOut = @checkOut AND idRoom=@id";

            SqlCommand cmd = cnn.CreateCommand();

            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@checkIn", book.CheckIn);
            cmd.Parameters.AddWithValue("@checkOut", book.Checkout);
            cmd.Parameters.AddWithValue("@id", book.RoomBook.Id);
            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("The record wasn't deleted");
                }
                else { return true; }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Deleting Booked A Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

        public bool IsBooked(int idRoom, DateTime dateTime)
        {
            if (dateTime == null) { throw new ArgumentException("Invalid Input Parameters"); }
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "SELECT idRoom FROM  [DataBaseHotel].[dbo].[Booking] ";
            sqlSentence += "WHERE @date BETWEEN [Booking].[CheckIn]  AND  [Booking].[CheckOut]";
            sqlSentence += " AND idRoom= @id";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.Parameters.AddWithValue("@date", dateTime);
            cmd.Parameters.AddWithValue("@id", idRoom);
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                return reader.HasRows;
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error On Data Base", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        #region Miembros de IDisposable

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (cnn != null)
                {
                    cnn.Dispose();
                    cnn = null;
                }
            }
        }

        #endregion

    }
}
